drop table if exists test_trigger_src_test;
drop table if exists test_trigger_des_tbl0;
CREATE TABLE test_trigger_src_test(id1 int,id2 int, id3 int) PARTITION BY RANGE (id1) (PARTITION P1 VALUES LESS THAN(5000));
CREATE TABLE test_trigger_des_tbl0(id1 INT, id2 INT, id3 INT);
CREATE OR REPLACE FUNCTION tri_insert_func01() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
	INSERT INTO test_trigger_des_tbl0 VALUES(NEW.id1, NEW.id2, NEW.id3);
	RETURN NEW;
END
$$ LANGUAGE PLPGSQL;

CREATE CONSTRAINT TRIGGER insert_trigger01 AFTER INSERT ON test_trigger_src_test FOR EACH ROW EXECUTE PROCEDURE tri_insert_func01();

INSERT INTO test_trigger_src_test VALUES(100,200,300);
SELECT * FROM test_trigger_src_test;

SELECT * FROM test_trigger_des_tbl0;

drop table if exists student cascade;
drop table if exists teacher;
create table student
(
    USER_ID int primary key,
    NAME varchar(10) not null,
    sal  int
);
create table teacher
(
    t_id int primary key,
    t_name varchar(10) not null,
    sal  int
);
insert into student values (2017100001, 'aaa');
insert into student values (2017100002, 'bbb');
insert into student values (2017100003, 'ccc');
insert into student values (2017100004, 'ddd');
insert into student values (2017100005, 'eee');
insert into student values (2017100006, 'fff');
insert into teacher values (2017100005, 'eee');
insert into teacher values (2017100006, '222');
insert into teacher values (2017100007, 'aaa');
insert into teacher values (2017100008, '444');
insert into teacher values (2017100009, '555');
insert into teacher values (2017100010, '666');
select rownum, * from (select * from student union all select * from teacher) as result where rownum < 8;
select rownum, * from (select * from student where rownum < 3 union select * from teacher where rownum != 4 order by 1 asc) as result where rownum != 3;
select rownum, * from (select rownum rowno, * from student union all select rownum rowno, * from teacher) as result where (rowno < 3 or rowno != 5) and rownum < 9;

drop table if exists T_CUST;
CREATE TABLE T_CUST(
  CUST_ID INT,
  NAME VARCHAR2(200),
  USER_ID INT
);

CREATE OR REPLACE PROCEDURE PRO1(V_MONTH INT) IS
  V_ID INT;
  v_cust_id VARCHAR2(200);
BEGIN
  EXECUTE IMMEDIATE 'SELECT T.NAME, S
  FROM (SELECT CUST_ID, T.NAME, SUM(SAL) S
          FROM T_CUST T
          LEFT JOIN student T1
            ON T.USER_ID = T1.USER_ID
         GROUP BY T.CUST_ID, T.NAME
        HAVING SUM(SAL) > 100) T
 WHERE S > 2000
 ORDER BY T.CUST_ID
  '
      INTO v_cust_id,V_ID;

END;
/

DROP TABLE IF EXISTS test_gin_student_column;
CREATE TABLE test_gin_student_column
(
    num int,
    data1 text,
    data2 text,
    test1 text,
    test2 text
) WITH (ORIENTATION = COLUMN)
PARTITION BY RANGE(num)
(
    PARTITION num1 VALUES LESS THAN(10000),
    PARTITION num2 VALUES LESS THAN(20000),
    PARTITION num3 VALUES LESS THAN(300000)
);

DROP TABLE IF EXISTS test_gin_student_row;
CREATE TABLE test_gin_student_row
(
    num int,
    data1 text,
    data2 text,
    test1 text,
    test2 text
)
PARTITION BY RANGE(num)
(
    PARTITION num1 VALUES LESS THAN(10000),
    PARTITION num2 VALUES LESS THAN(20000),
    PARTITION num3 VALUES LESS THAN(300000)
);

CREATE INDEX test_gin_student_index_column1 ON test_gin_student_column USING GIN(to_tsvector('english', data1),to_tsvector('english', data2),to_tsvector('english', test1),to_tsvector('english', test2)) LOCAL ;
CREATE INDEX test_gin_student_index_column2 ON test_gin_student_column USING GIN(to_tsvector('english', data1),to_tsvector('english', data2),to_tsvector('english', test1),to_tsvector('english', test2))  LOCAL
(
    PARTITION data2_index_1,
    PARTITION data2_index_2,
    PARTITION data2_index_3
) ;
CREATE INDEX test_gin_student_index_row1 ON test_gin_student_row USING GIN(to_tsvector('english', data1),to_tsvector('english', data2),to_tsvector('english', test1),to_tsvector('english', test2)) LOCAL ;
CREATE INDEX test_gin_student_index_row2 ON test_gin_student_row USING GIN(to_tsvector('english', data1),to_tsvector('english', data2),to_tsvector('english', test1),to_tsvector('english', test2)) LOCAL
(
    PARTITION data2_index_1,
    PARTITION data2_index_2,
    PARTITION data2_index_3
) ;

INSERT INTO test_gin_student_column SELECT num, md5(random()::text), md5(random()::text) , md5(random()::text) , md5(random()::text) FROM (SELECT * FROM generate_series(1,200000) AS num) AS x;
INSERT INTO test_gin_student_column VALUES(1, 'China, officially the People''s Republic of China (PRC), located in Asia, is the world''s most populous state.', 'China','China', 'China, officially the People');
INSERT INTO test_gin_student_column VALUES(2, 'America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley.', 'America','China', 'China, officially the People');
INSERT INTO test_gin_student_column VALUES(3, 'England is a country that is part of the United Kingdom. It shares land borders with Scotland to the north and Wales to the west.', 'England','China', 'China, officially the People');
INSERT INTO test_gin_student_column VALUES(4, 'Australia, officially the Commonwealth of Australia, is a country comprising the mainland of the Australian continent, the island of Tasmania, and numerous smaller islands.', 'Australia','China', 'China, officially the People');
INSERT INTO test_gin_student_column VALUES(5, 'Russia, also officially known as the Russian Federation, is a sovereign state in northern Eurasia.', 'Russia','China', 'China, officially the People');
INSERT INTO test_gin_student_column VALUES(6, 'Japan is an island country in East Asia.', 'Japan','China', 'China, officially the People');
INSERT INTO test_gin_student_column VALUES(7, 'Germany, officially the Federal Republic of Germany, is a sovereign state and federal parliamentary republic in central-western Europe.','China', 'China, officially the People');
INSERT INTO test_gin_student_column VALUES(8, 'France, is a sovereign state comprising territory in western Europe and several overseas regions and territories.', 'France');
INSERT INTO test_gin_student_column VALUES(9, 'Italy officially the Italian Republic, is a unitary parliamentary republic in Europe.', 'Italy','China', 'China, officially the People');
INSERT INTO test_gin_student_column VALUES(10, 'India, officially the Republic of India, is a country in South Asia.', 'India','China', 'China, officially the People');
INSERT INTO test_gin_student_column VALUES(11, 'Brazil, officially the Federative Republic of Brazil, is the largest country in both South America and Latin America.', 'Brazil','China', 'China, officially the People');
INSERT INTO test_gin_student_column VALUES(12, 'Canada is a country in the northern half of North America.', 'Canada','China', 'China, officially the People');
INSERT INTO test_gin_student_column VALUES(13, 'Mexico, officially the United Mexican States, is a federal republic in the southern part of North America.', 'Mexico','China', 'China, officially the People');
INSERT INTO test_gin_student_column VALUES(13, 'Mexico, officially the United Mexican States, is a federal republic in the southern part of North America.', 'Mexico','China', 'China, officially the People');
INSERT INTO test_gin_student_column VALUES(14, 'Mexico, officially the United Mexican States, is a federal republic in the southern part of North America.', 'Mexicox','China', 'China, officially the People');
INSERT INTO test_gin_student_column VALUES(20001, 'Mexico, officially the United Mexican States, is a federal republic in the southern part of North America.', 'Mexicos','China', 'China, officially the People');

INSERT INTO test_gin_student_row SELECT num, md5(random()::text), md5(random()::text) , md5(random()::text) , md5(random()::text) FROM (SELECT * FROM generate_series(1,200000) AS num) AS x;
INSERT INTO test_gin_student_row VALUES(1, 'China, officially the People''s Republic of China (PRC), located in Asia, is the world''s most populous state.', 'China','China', 'China, officially the People');
INSERT INTO test_gin_student_row VALUES(2, 'America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley.', 'America','China', 'China, officially the People');
INSERT INTO test_gin_student_row VALUES(3, 'England is a country that is part of the United Kingdom. It shares land borders with Scotland to the north and Wales to the west.', 'England','China', 'China, officially the People');
INSERT INTO test_gin_student_row VALUES(4, 'Australia, ***officially the Commonwealth of Australia, is a country comprising the mainland of the Australian continent, the island of Tasmania, and numerous smaller islands.', 'Australia','China', 'China, officially the People');
INSERT INTO test_gin_student_row VALUES(5, 'Russia, also officially known as the Russian Federation, is a sovereign state in northern Eurasia.', 'Russia','China', 'China, officially the People');
INSERT INTO test_gin_student_row VALUES(6, 'Japan is an island country in East Asia.', 'Japan','China', 'China, officially the People');
INSERT INTO test_gin_student_row VALUES(7, 'Germany, officially the Federal Republic of Germany, is a sovereign state and federal parliamentary republic in central-western Europe.','China', 'China, officially the People');
INSERT INTO test_gin_student_row VALUES(8, 'France, is a sovereign state comprising territory in western Europe and several overseas regions and territories.', 'France');
INSERT INTO test_gin_student_row VALUES(9, 'Italy officially 随便写点啥 the Italian Republic, is a unitary parliamentary republic in Europe.', 'Italy','China', 'China, officially the People');
INSERT INTO test_gin_student_row VALUES(10, 'India, officially the Republic of India, is a country in South Asia.', 'India','China', 'China, officially the People');
INSERT INTO test_gin_student_row VALUES(11, 'Brazil, officially the Federative Republic of Brazil, is the largest country in both South America and Latin America.', 'Brazil','China', 'China, officially the People');
INSERT INTO test_gin_student_row VALUES(12, 'Canada is a country in the northern half of North America.', 'Canada','China', 'China, officially the People');
INSERT INTO test_gin_student_row VALUES(13, 'Mexico, officially the United Mexican States, 、is a federal republic in the southern part of North America.', 'Mexico','China', 'China, officially the People');
INSERT INTO test_gin_student_row VALUES(13, 'Mexico, officially the United Mexican States, is a federal republic in the southern part of North America.', 'Mexico','China', 'China, officially the People');
INSERT INTO test_gin_student_row VALUES(14, 'Mexico, officially the United Mexican ）））））States, is a federal republic in the southern part of North America.', 'Mexicox','China', 'China, officially the People');
INSERT INTO test_gin_student_row VALUES(20001, 'Mexico, officially the United Mexican States, is a federal republic in the southern part of North America.', 'Mexicos','China', 'China, officially the People');

drop table if exists student cascade;
drop user if exists mv_test;
create table student
(
    id int primary key,
    name varchar(10) not null
);
insert into student values (1, 'aaa');
insert into student values (2, 'bbb');
insert into student values (3, 'ccc');
insert into student values (4, 'ddd');
insert into student values (5, 'eee');
insert into student values (6, 'fff');
create materialized view student_mv (id, name) as select * from student;
